package zsrt.com.excel;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Collection;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import zsrt.com.dao.ExamineeDao;


public class SetValue {
	
	/**
	 * 存入excel表格
	 * @param map
	 * @return
	 */
	public static String bmsRRnoExcel(Map<String,ExamineeDao> map) {
		String filename = "D:\\zhongshen\\workspace\\javase-examineeSystem\\data.xls";// D:\File\report20161216130817.xls
		HSSFWorkbook wb = new HSSFWorkbook();// 创建工作薄
		// 创建字体样式
		HSSFFont font = wb.createFont();
		font.setFontName("Verdana");
		font.setBoldweight((short) 100);
		font.setFontHeight((short) 200);
		font.setColor(HSSFColor.BLACK.index);
		// 创建单元格样式
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setFillForegroundColor(HSSFColor.WHITE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// 设置边框
		style.setBottomBorderColor(HSSFColor.BLACK.index);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setFont(font);// 设置字体
		// 日期格式化
		HSSFCellStyle style1 = wb.createCellStyle();
		HSSFDataFormat format = wb.createDataFormat();
		style1.setDataFormat(format.getFormat("yyyy-MM-dd"));

		//就是左下角文件的名字
		HSSFSheet sheet = wb.createSheet("data.xls");// 创建工作表，名称为test
		// 设置excel每列宽度
		sheet.setColumnWidth(0, 4500);
		sheet.setColumnWidth(1, 3500);
		sheet.setColumnWidth(2, 3500);
		sheet.setColumnWidth(3, 3500);
		sheet.setColumnWidth(4, 3500);
		sheet.setColumnWidth(5, 4500);
		sheet.setColumnWidth(6, 4500);
		sheet.setColumnWidth(7, 4500);
		sheet.setColumnWidth(8, 3500);
		sheet.setColumnWidth(9, 3500);
		// 创建第一行
		HSSFRow row = sheet.createRow(0);
		row.setHeight((short) 500);// 设定行的高度
		HSSFCell cell = row.createCell(0);
		cell.setCellStyle(style);
		cell.setCellValue("准考证号码");
		cell = row.createCell(1);
		cell.setCellStyle(style);
		cell.setCellValue("密码");
		cell = row.createCell(2);
		cell.setCellStyle(style);
		cell.setCellValue("姓名");
		cell = row.createCell(3);
		cell.setCellStyle(style);
		cell.setCellValue("年龄");
		cell = row.createCell(4);
		cell.setCellStyle(style);
		cell.setCellValue("所属省份");
		cell = row.createCell(5);
		cell.setCellStyle(style);
		cell.setCellValue("户籍所在地");
		cell = row.createCell(6);
		cell.setCellStyle(style);
		cell.setCellValue("联系方式");
		cell = row.createCell(7);
		cell.setCellStyle(style);
		cell.setCellValue("语文分数");
		cell = row.createCell(8);
		cell.setCellStyle(style);
		cell.setCellValue("数学分数");
		cell = row.createCell(9);
		cell.setCellStyle(style);
		cell.setCellValue("英语分数");
		cell = row.createCell(10);
		cell.setCellStyle(style);
		cell.setCellValue("总分");

		int a = 0;
		Collection<ExamineeDao> values = map.values();
		for(ExamineeDao examineeDao : values){
			a++;
			row = sheet.createRow(a);
			row.setHeight((short) 500);// 设定行的高度
			cell = row.createCell(0);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(style);
			
			cell.setCellValue(examineeDao.getId());
			cell = row.createCell(1);
			cell.setCellStyle(style);
			cell.setCellValue(examineeDao.getPassword());
			cell = row.createCell(2);
			cell.setCellStyle(style);
			cell.setCellValue(examineeDao.getName());
			cell = row.createCell(3);
			cell.setCellStyle(style);
			cell.setCellValue(examineeDao.getAge());
			cell = row.createCell(4);
			cell.setCellStyle(style);
			cell.setCellValue(examineeDao.getProvince());
			cell = row.createCell(5);
			cell.setCellStyle(style);// 设置为日期格式
			cell.setCellValue(examineeDao.getCity());
			cell = row.createCell(6);
			cell.setCellStyle(style);// 设置为日期格式
			cell.setCellValue(examineeDao.getPhone());
			cell = row.createCell(7);
			cell.setCellStyle(style);
			cell.setCellValue(examineeDao.getChinese());
			cell = row.createCell(8);
			cell.setCellStyle(style);
			cell.setCellValue(examineeDao.getMath());
			cell = row.createCell(9);
			cell.setCellStyle(style);
			cell.setCellValue(examineeDao.getEnglish());
			cell = row.createCell(10);
			cell.setCellStyle(style);
			cell.setCellValue(examineeDao.getTotal());
		}

		ByteArrayOutputStream os = new ByteArrayOutputStream();
		try {
			wb.write(os);
		} catch (IOException e) {
			e.printStackTrace();
		}

		byte[] xls = os.toByteArray();
		File file = new File(filename);
		OutputStream out = null;
		try {
			out = new FileOutputStream(file);
			try {
				out.write(xls);
			} catch (IOException e) {
				e.printStackTrace();
			}
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		}
		return filename;
	}
}
